/*
 * @Author: 火红色祥云 809880192@qq.com
 * @Date: 2023-10-04 18:47:29
 * @LastEditors: 火红色祥云 809880192@qq.com
 * @LastEditTime: 2023-10-04 23:01:34
 * @FilePath: /imx6ull-app/26_Sqlite3/db.c
 * @Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 */
#include "db.h"

/**
 * sqlite3_open
 * sqlite3_close
 * sqlite3_exec
*/
//==================================================================================================
//| function |  创建一个数据库文件
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库文件名    数据库句柄
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_OpenDB(const char *DB_FileName,sqlite3 **DB_Handle)
{
    return sqlite3_open(DB_FileName,DB_Handle);
}

//==================================================================================================
//| function |  关闭一个数据库文件
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_CloseDB(sqlite3 *DB_Handle)
{
    return sqlite3_close(DB_Handle);
}

//==================================================================================================
//| function |  创建一个数据库表
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄  数据库表名
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_CreatDBTable(sqlite3 *DB_Handle,char *TableName)
{
    char *errmsg = NULL;
    char exe_sql[1024];
    memset(exe_sql, 0, sizeof(exe_sql));
    if (DB_Handle == NULL)
    {
        return -1;
    }

    char *sql = "CREATE TABLE IF NOT EXISTS %s (\
                [p_id] INTEGER PRIMARY KEY AUTOINCREMENT,\
                [r_value] INTEGER,\
                [d_value] INTEGER,\
                [max_value] INTEGER,\
                [min_value] INTEGER,\
                [offset] INTEGER\
                );";
    sprintf(exe_sql,sql,TableName);

    int ret = sqlite3_exec(DB_Handle,exe_sql,NULL,NULL,&errmsg);
    if (ret != SQLITE_OK)
    {

        return -2;
    }
    
    return 0;
}

//==================================================================================================
//| function |  替换数据库的值
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄  数据库表名  替换的数据结构体
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  存在就更换数据，替换原来的数据
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================

int DB_ReplaceDBData(sqlite3 *DB_Handle,char *TableName,DB_DataRow dataRow)
{
    const char *error = NULL;
    sqlite3_stmt *Stmt = NULL;
    int index = 0;
    int ret = 0;
    if (DB_Handle == NULL)
    {
        return -1;
    }
    char *sql = "REPLACE INTO %s (p_id, r_value, d_value, max_value, min_value, offset) VALUES \
                                    (:p_id, :r_value, :d_value,  :max_value, :min_value,  :offset )";
    char exe_sql[1024];
    memset(exe_sql, 0, sizeof(exe_sql));
    sprintf(exe_sql,sql,TableName);

    ret = sqlite3_prepare_v2(DB_Handle,exe_sql,strlen(exe_sql),&Stmt,&error);
    if (ret != SQLITE_OK)
    {
        return -1;
    }
    
    index = sqlite3_bind_parameter_index(Stmt,":p_id");
    ret = sqlite3_bind_int(Stmt,index,dataRow.RTE_ID);
    index = sqlite3_bind_parameter_index(Stmt, ":r_value");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Value);

    index = sqlite3_bind_parameter_index(Stmt,":d_value");
    ret = sqlite3_bind_int(Stmt,index,dataRow.Defalult_Value);
    index = sqlite3_bind_parameter_index(Stmt, ":offset");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Offset);

    index = sqlite3_bind_parameter_index(Stmt, ":max_value");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Max_Value);
    index = sqlite3_bind_parameter_index(Stmt, ":min_value");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Min_Value);

    ret = sqlite3_step(Stmt);
    ret = sqlite3_finalize(Stmt); //销毁语句
    return 0;
}

//==================================================================================================
//| function |  向数据库中插入数据
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄  数据库表名  插入的数据结构体
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  插入失败保存原来的数据
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_InsertDBData(sqlite3 *DB_Handle,char *TableName,DB_DataRow dataRow)
{
    const char *error = NULL;
    sqlite3_stmt *Stmt = NULL;
    int index = 0;
    int ret = 0;
    if (DB_Handle == NULL)
    {
        return -1;
    }
    char *sql = "INSERT INTO %s (p_id, r_value, d_value, max_value, min_value, offset) VALUES \
                                    (:p_id, :r_value, :d_value,  :max_value, :min_value,  :offset )";
    char exe_sql[1024];
    memset(exe_sql, 0, sizeof(exe_sql));
    sprintf(exe_sql,sql,TableName);

    ret = sqlite3_prepare_v2(DB_Handle,exe_sql,strlen(exe_sql),&Stmt,&error);
    if (ret != SQLITE_OK)
    {
        return -1;
    }
    
    index = sqlite3_bind_parameter_index(Stmt,":p_id");
    ret = sqlite3_bind_int(Stmt,index,dataRow.RTE_ID);
    index = sqlite3_bind_parameter_index(Stmt, ":r_value");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Value);

    index = sqlite3_bind_parameter_index(Stmt,":d_value");
    ret = sqlite3_bind_int(Stmt,index,dataRow.Defalult_Value);
    index = sqlite3_bind_parameter_index(Stmt, ":offset");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Offset);

    index = sqlite3_bind_parameter_index(Stmt, ":max_value");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Max_Value);
    index = sqlite3_bind_parameter_index(Stmt, ":min_value");
    ret = sqlite3_bind_int(Stmt, index, dataRow.Min_Value);

    ret = sqlite3_step(Stmt);
    ret = sqlite3_finalize(Stmt); //销毁语句
    return 0;
}

//==================================================================================================
//| function |  更新指定索引的数据
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄  数据库表名    索引    更新的数据
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_UpdateDBData(sqlite3 *DB_Handle,char *TableName,int RTE,int Value)
{
    const char *error = NULL;
    sqlite3_stmt *Stmt = NULL;
    int index = 0;
    int ret = 0;
    if (DB_Handle == NULL)
    {
        return -1;
    }

    char *sql = "UPDATE %s SET r_value=:r_value WHERE p_id = %d";
    char exe_sql[1024];
    memset(exe_sql, 0, sizeof(exe_sql));
    sprintf(exe_sql,sql,TableName,RTE);

    ret = sqlite3_prepare_v2(DB_Handle,exe_sql,strlen(exe_sql),&Stmt,&error);
    if (ret != SQLITE_OK)
    {
        return -1;
    }

    index = sqlite3_bind_parameter_index(Stmt, ":r_value");
    ret = sqlite3_bind_int(Stmt, index, Value);

    ret = sqlite3_step(Stmt);

    ret = sqlite3_finalize(Stmt); //销毁语句

    return 0;
}

//==================================================================================================
//| function |  查询指定索引指定行所有的数据
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄  数据库表名 查询索引（指定行）   保存数据的结构体指针
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_SelectDBData(sqlite3 *DB_Handle,char *TableName,int RTE_ID,DB_DataRow *dataRow)
{
    const char *error = NULL;
    sqlite3_stmt *Stmt = NULL;
    int index = 0;
    int ret = 0;
    if (DB_Handle == NULL)
    {
        return -1;
    }
    if (dataRow == NULL)
    {
        return -1;
    }
    

    char *sql = "SELECT r_value, d_value, max_value, min_value, offset FROM %s where p_id = %d";
    char exe_sql[1024];
    memset(exe_sql, 0, sizeof(exe_sql));
    sprintf(exe_sql,sql,TableName,RTE_ID);

    ret = sqlite3_prepare_v2(DB_Handle,exe_sql,strlen(exe_sql),&Stmt,&error);
    if (ret != SQLITE_OK)
    {
        return -1;
    }
    ret = sqlite3_step(Stmt);

    if (SQLITE_ROW == ret)
    {
        dataRow->Value = sqlite3_column_int(Stmt, 0);
        dataRow->Defalult_Value = sqlite3_column_int(Stmt, 1);
        dataRow->Max_Value= sqlite3_column_int(Stmt, 2);
        dataRow->Min_Value = sqlite3_column_int(Stmt, 3);
        dataRow->Offset = sqlite3_column_int(Stmt, 4);
    }
    else
    {
        printf("DB_SelectSystemData sql exec error, Code::%d!\n", ret);
        printf("DB_SelectSystemData ERROR::%s\n", sqlite3_errmsg(DB_Handle));
    }

    ret = sqlite3_finalize(Stmt); //销毁语句

    return 0;
}

//==================================================================================================
//| function |  查询指定范围行的数据
//|--------- |--------------------------------------------------------------------------------------
//| parameter|  数据库句柄  数据库表名 起始行   查询的行数  保存数据的结构体指针
//|--------- |--------------------------------------------------------------------------------------
//|  detail  |  
//|--------- |--------------------------------------------------------------------------------------
//|  envir   | linux/stm32
//|--------- |--------------------------------------------------------------------------------------
//|  return  | -1:失败   0:成功
//|--------- |--------------------------------------------------------------------------------------
//|  version |    dete     |  atuther     | description
//|--------- |-------------|--------------|---------------------------------------------------------
//|  V1.0    |  20230910   |    lux       | 
//==================================================================================================
int DB_SelectDBRangeData(sqlite3 *DB_Handle,char *TableName,int RTE_StartID,int Num,DB_DataRow dataRow[])
{
    const char *error = NULL;
    sqlite3_stmt *Stmt = NULL;
    int index = 0;
    int ret = 0;
    if (DB_Handle == NULL)
    {
        return -1;
    }
    if (dataRow == NULL)
    {
        return -1;
    }
    

    char *sql = "SELECT r_value, d_value, max_value, min_value, offset FROM %s where p_id >= %d and p_id < %d";
    char exe_sql[1024];
    memset(exe_sql, 0, sizeof(exe_sql));
    sprintf(exe_sql,sql,TableName,RTE_StartID,RTE_StartID+Num);

    ret = sqlite3_prepare_v2(DB_Handle,exe_sql,strlen(exe_sql),&Stmt,&error);
    if (ret != SQLITE_OK)
    {
        return -1;
    }
    for (int i = 0; i < Num; i++)
    {
        ret = sqlite3_step(Stmt);
        if (SQLITE_ROW == ret)
        {
            dataRow[i].RTE_ID = RTE_StartID+i;
            dataRow[i].Value = sqlite3_column_int(Stmt, 0);
            dataRow[i].Defalult_Value = sqlite3_column_int(Stmt, 1);
            dataRow[i].Max_Value= sqlite3_column_int(Stmt, 2);
            dataRow[i].Min_Value = sqlite3_column_int(Stmt, 3);
            dataRow[i].Offset = sqlite3_column_int(Stmt, 4);
            ret=0;
        }
        else if (SQLITE_DONE == ret)
        {
            if ((i==0)||(i<Num))
            {
                ret = -2;
            }
            break;
        }
        
        else
        {
            printf("DB_SelectSystemData sql exec error, Code::%d!\n", ret);
            printf("DB_SelectSystemData ERROR::%s\n", sqlite3_errmsg(DB_Handle));
            ret=-1;
            break;
        }
    }
    
    ret = sqlite3_finalize(Stmt); //销毁语句

    return 0;
}
